<?php
class Sql
{
    protected $_dbHandle;
    protected $_result;
    protected $_where = ' 1 ';
    protected $_field = ' * ';
    protected $_limit = 10;
    protected $_page = 1;
    protected $_order = '';
    protected $wheres;
    protected $_joinTable;
    protected $_on;
    protected $_pk;//主键
    // 连接数据库
    public function connect($host, $port, $user, $pass, $dbname)
    {
        try {
            $dsn = sprintf("mysql:host=%s;port=%s;dbname=%s;charset=utf8", $host, $port, $dbname);
            $this->_dbHandle = new PDO($dsn, $user, $pass, array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC));
        } catch (PDOException $e) {
            exit('错误: ' . $e->getMessage());
        }
        $this->getPk();
    }

    public function getPk() {
        $sql = "desc user";
        $sth = $this->_dbHandle->prepare($sql);
        $sth->execute();
        $fileds = $sth->fetchAll();
        if(isset($fileds) && is_array($fileds) && count($fileds)>0 ) {
            foreach ($fileds as $v) {
                if($v['Key'] == 'PRI') {
                    $this->_pk = $v['Field'];
                }
            }
        }
    }
    // 查询所有
    public function selectAll1()
    {
        $sql = sprintf("select * from `%s`", $this->_table);
        $sth = $this->_dbHandle->prepare($sql);
        $sth->execute();

        return $sth->fetchAll();
    }
    // 根据条件 (id) 查询
    public function select1($id)
    {
        $sql = sprintf("select * from `%s` where `id` = '%s'", $this->_table, $id);
        $sth = $this->_dbHandle->prepare($sql);
        $sth->execute();

        return $sth->fetch();
    }

    // 自定义SQL查询，返回影响的行数
    public function query($sql)
    {
        $sth = $this->_dbHandle->prepare($sql);
        $sth->execute();

        return $sth->rowCount();
    }

    // 将数组转换成插入格式的sql语句
    private function formatInsert($data)
    {
        $fields = array();
        $values = array();
        foreach ($data as $key => $value) {
            $fields[] = sprintf("`%s`", $key);
            $values[] = sprintf("'%s'", $value);
        }

        $field = implode(',', $fields);
        $value = implode(',', $values);

        return sprintf("(%s) values (%s)", $field, $value);
    }

    // 将数组转换成更新格式的sql语句
    private function formatUpdate($data)
    {
        $fields = array();
        foreach ($data as $key => $value) {
            $fields[] = sprintf("`%s` = '%s'", $key, $value);
        }
        return implode(',', $fields);
    }


    public function field($str = '') {
        $this->_field = $str ? $str :" * ";
        return $this;
    }

    public function select() {
        if(isset($this->wheres) && count($this->wheres) > 0) {
            $fields = array();
            foreach ($this->wheres as $key => $value) {
                $fields[] = sprintf("%s %s '%s'", $value[0], $value[1], $value[2]);
            }
            $this->_where = implode(' and ', $fields);
        } else {
            $this->_where = " 1 ";
        }

        if($this->_joinTable) {
            if(!$this->_order) {
                $this->sql = sprintf("select %s from `%s` INNER JOIN %s ON %s where %s LIMIT %s,%s", $this->_field, $this->_table, $this->_joinTable, $this->_on, $this->_where, ($this->_page-1)*$this->_limit, $this->_limit);
            } else {
                $this->sql = sprintf("select %s from `%s` INNER JOIN %s ON %s where %s  ORDER BY %s LIMIT %s,%s", $this->_field, $this->_table, $this->_joinTable, $this->_on, $this->_where, $this->_order, ($this->_page-1)*$this->_limit, $this->_limit);
            }

        } else {
            if(!$this->_order) {
                $this->sql = sprintf("select %s from `%s` where %s  LIMIT %s,%s", $this->_field, $this->_table, $this->_where, ($this->_page-1)*$this->_limit, $this->_limit);
            } else {
                $this->sql = sprintf("select %s from `%s` where %s  ORDER BY %s LIMIT %s,%s", $this->_field, $this->_table, $this->_where, $this->_order, ($this->_page-1)*$this->_limit, $this->_limit);
            }
        }

        $sth = $this->_dbHandle->prepare($this->sql);
        $sth->execute();
        return $sth->fetchAll();
    }

    public function getLastSql() {
        if(isset($this->wheres) && count($this->wheres) > 0) {
            $fields = array();
            foreach ($this->wheres as $key => $value) {
                $fields[] = sprintf("%s %s '%s'", $value[0], $value[1], $value[2]);
            }
            $this->_where = implode(' and ', $fields);
        } else {
            $this->_where = " 1 ";
        }

        if($this->_joinTable) {
            if(!$this->_order) {
                $this->sql = sprintf("select %s from `%s` INNER JOIN %s ON %s where %s LIMIT %s,%s", $this->_field, $this->_table, $this->_joinTable, $this->_on, $this->_where, ($this->_page-1)*$this->_limit, $this->_limit);
            } else {
                $this->sql = sprintf("select %s from `%s` INNER JOIN %s ON %s where %s  ORDER BY %s LIMIT %s,%s", $this->_field, $this->_table, $this->_joinTable, $this->_on, $this->_where, $this->_order, ($this->_page-1)*$this->_limit, $this->_limit);
            }

        } else {
            if(!$this->_order) {
                $this->sql = sprintf("select %s from `%s` where %s  LIMIT %s,%s", $this->_field, $this->_table, $this->_where, ($this->_page-1)*$this->_limit, $this->_limit);
            } else {
                $this->sql = sprintf("select %s from `%s` where %s  ORDER BY %s LIMIT %s,%s", $this->_field, $this->_table, $this->_where, $this->_order, ($this->_page-1)*$this->_limit, $this->_limit);
            }
        }
        return $this->sql;
    }

    public function count($filed=" * ") {
        $this->_field = $filed;
        if(isset($this->wheres) && count($this->wheres) > 0) {
            $fields = array();
            foreach ($this->wheres as $key => $value) {
                $fields[] = sprintf("%s %s '%s'", $value[0], $value[1], $value[2]);
            }
            $this->_where = implode(' and ', $fields);
        } else {
            $this->_where = " 1 ";
        }

        $this->sql = sprintf("select count(%s) as count from `%s` where %s ", $this->_field, $this->_table, $this->_where);

        if($this->_joinTable){
            $this->sql = sprintf("select count(%s) as count from `%s` INNER JOIN %s ON %s where %s  ORDER BY %s LIMIT %s,%s", $this->_field, $this->_table, $this->_joinTable, $this->_on, $this->_where, $this->_order, ($this->_page-1)*$this->_limit, $this->_limit);
        }

        $sth = $this->_dbHandle->prepare($this->sql);
        $sth->execute();
        $count = $sth->fetch();
        return $count['count'] ?? 0;
    }

    public function where() {
        $numargs = func_num_args ();
        $args = func_get_args();
        if($this->wheres) {
            $where = $this->wheres;
        }
        if($numargs == 2){
            $where[] = [$args[0], '=', $args[1]];
        }else{
            $where[] = [$args[0], $args[1], $args[2]];
        }
        $this->wheres = $where;

        return $this;
    }

    public function limit($limit = 10) {
        $this->_limit = $limit;
        return $this;
    }

    public function page($page = 1) {
        $this->_page = $page;
        return $this;
    }

    public function order($order = '') {
        $this->_order = $order;
        return $this;
    }

    public function join($other_table, $on) {
        $this->_joinTable = $other_table;
        $this->_on = $on;
        return $this;
    }

    // 根据主键查询单条
    public function find($id=0) {
        $is_array = false;
        if(is_array($id)) {
            $is_array = true;
            $id = implode(',', $id);
            $this->_where = $this->_pk.' in('.$id.')';
        } else {
            $this->_where = $this->_pk.'='.$id;
        }

        $this->sql = sprintf("select %s from `%s` where %s limit %s,%s", $this->_field, $this->_table, $this->_where, ($this->_page-1)*$this->_limit, $this->_limit);
        $sth = $this->_dbHandle->prepare($this->sql);
        $sth->execute();
        if($is_array) {
            return $sth->fetchAll();
        } else {
            return $sth->fetch();
        }
    }

    // 根据条件 (id) 删除
    public function delete($id=0) {
        if($id != 0) {
            if(is_array($id)) {
                $is_array = true;
                $id = implode(',', $id);
                $this->_where = $this->_pk.' in('.$id.')';
            } else {
                $this->_where = $this->_pk.'='.$id;
            }
        }else {
            if(isset($this->wheres) && count($this->wheres) > 0) {
                $fields = array();
                foreach ($this->wheres as $key => $value) {
                    $fields[] = sprintf("%s %s '%s'", $value[0], $value[1], $value[2]);
                }
                $this->_where = implode(' and ', $fields);
            } else {
                $this->_where = 0;
            }
        }

        if($this->_where) {
            $sql = sprintf("delete from `%s` where %s", $this->_table, $this->_where);
            $sth = $this->_dbHandle->prepare($sql);
            $sth->execute();
            return $sth->rowCount();
        } else {
            exit('缺少where条件');
        }
    }

    // 新增数据
    public function save($data) {
        $is_update = false;
        foreach($data as $k => $val) {
            if($k == $this->_pk) {
                $is_update = true;
                $id = $val;
            }
        }

        //编辑
        if($is_update && $id) {
            $sql = sprintf("update `%s` set %s where %s = '%s'", $this->_table, $this->formatUpdate($data), $this->_pk, $id);
            return $this->query($sql);
        } else {
            //新增
            $sql = sprintf("insert into `%s` %s", $this->_table, $this->formatInsert($data));
            return $this->query($sql);
        }

    }

    // 修改数据
    public function update($data)
    {
        if(isset($this->wheres) && count($this->wheres) > 0) {
            $fields = array();
            foreach ($this->wheres as $key => $value) {
                $fields[] = sprintf("%s %s '%s'", $value[0], $value[1], $value[2]);
            }
            $this->_where = implode(' and ', $fields);
        } else {
            exit('缺少where条件');
        }

        $sql = sprintf("update `%s` set %s where %s ", $this->_table, $this->formatUpdate($data), $this->_where);

        return $this->query($sql);
    }

    //开启事务
    public function beginTransaction() {
        $this->_dbHandle->beginTransaction();
    }

    //提交事务
    public function commit() {
        $this->_dbHandle->commit();
    }

    //回滚事务
    public function rollback() {
        $this->_dbHandle->rollback();
    }


}